﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Services.Description;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;

namespace MockPrjTeam2.DAO
{
    public class GovOfficeRegionDao
    {
        private static GovOfficeRegionDao _govOfficeRegionDao;
        public static GovOfficeRegionDao Instance
        {
            get
            {
                if (_govOfficeRegionDao == null)
                    _govOfficeRegionDao = new GovOfficeRegionDao();

                return _govOfficeRegionDao;
            }
        }

        //method GetAllGovOfficeRegion()
        public DataTable GetAll()
        {
            const string query = "Select GovtOfficeRegionName,Description,GovOfficeRegion.IsActive,County.CountyName,GovOfficeRegionID from GovOfficeRegion inner join County on GovOfficeRegion.CountyID=County.CountyID where GovOfficeRegion.IsActive=1 order by GovOfficeRegionID asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }
        //method GetGovOfficeRegionActive()
        public DataTable GetActive()
        {
            string query = "Select GovtOfficeRegionName,Description,GovOfficeRegion.IsActive,County.CountyName,GovOfficeRegionID from GovOfficeRegion inner join County on GovOfficeRegion.CountyID=County.CountyID order by GovOfficeRegionID asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }
        //method GetGovOfficeRegionById()
        public DataTable GetById(int idGovOfficeRegion)
        {
            string query =
               "select GovOfficeRegionID,GovtOfficeRegionName,Description,County.CountyName as CountyName, County.CountyID from GovOfficeRegion inner join County on GovOfficeRegion.CountyID=County.CountyID where GovOfficeRegionID=@Id";

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int);
            sqlParameters[0].Value = idGovOfficeRegion;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
        
        public DataTable StartWithByNames(string startWith,int flag)
        {
            string query = @"Select GovOfficeRegionID,GovtOfficeRegionName,Description,GovOfficeRegion.IsActive,County.Countyname from GovOfficeRegion
inner join County on  GovOfficeRegion.CountyID=County.CountyID";
            if (flag == 1)
            {
                query = startWith.Equals("all") ? query + " order by GovOfficeRegion.GovtOfficeRegionName asc" : query +
                    " WHERE  (GovOfficeRegion.GovtOfficeRegionName LIKE '['+@GovtOfficeRegionName+']%')" +
                    " order by GovOfficeRegion.GovtOfficeRegionName asc";
            }
            else
            {
                query = startWith.Equals("all") ? query +
                    " WHERE GovOfficeRegion.IsActive = 1 order by GovOfficeRegion.GovtOfficeRegionName asc" : query +
                    " WHERE  (GovOfficeRegion.GovtOfficeRegionName LIKE '['+@GovtOfficeRegionName+']%') And GovOfficeRegion.IsActive = 1" +
                    " order by GovOfficeRegion.GovtOfficeRegionName asc";
            }
            var sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@GovtOfficeRegionName", SqlDbType.NVarChar) { Value = startWith };
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }       
    }
}